﻿CREATE PROCEDURE [dbo].[spNewRefunds]
	@OrderID int,
	@SelectNewRefunds bit = 1
AS
	declare @UserID int
	select @UserID = UserID from tblUser where UserName = SYSTEM_USER

	delete from tblRefund where OrderID = @OrderID and Cost = 0

	-- Refunds not linked to product or tax are considered shipping refunds
	insert into tblRefund( OrderID, Cost, MaxCost, RefundDate, UserID )
	select @OrderID, 0, case when coalesce(T2.Cost, 0) < T1.Shipping then T1.Shipping - coalesce(T2.Cost, 0) else 0 end, GETDATE(), @UserID
	from tblOrderHeader T1
		left outer join ( select OrderID, SUM( Cost ) as Cost from tblRefund 
			where OrderDetailID is null and OrderTaxID is null
			group by OrderID ) as T2 on T1.OrderID = T2.OrderID
	where T1.OrderID = @OrderID
		and T1.Shipping > coalesce(T2.Cost, 0)

	-- Product refund
	insert into tblRefund( OrderID, Cost, MaxCost, RefundDate, UserID, OrderDetailID )
	select @OrderID, 0, ( ( T1.Qty * T1.UnitPrice ) - T1.Discount ) - coalesce(T2.Cost, 0), GETDATE(), @UserID, T1.OrderDetailID
	from tblOrderDetail T1
		left outer join ( select OrderDetailID, SUM( Cost ) as Cost 
			from tblRefund 
			group by OrderDetailID ) as T2 on T1.OrderDetailID = T2.OrderDetailID
	where OrderID = @OrderID
		and ( ( T1.Qty * T1.UnitPrice ) - T1.Discount ) > coalesce(T2.Cost, 0)

	-- Tax refund
	insert into tblRefund( OrderID, Cost, MaxCost, RefundDate, UserID, OrderTaxID )
	select @OrderID, 0, T1.Amount - coalesce( T2.Cost, 0 ), GETDATE(), @UserID, t1.OrderTaxID
	from tblOrderTax T1
		left outer join ( select OrderTaxID, SUM(Cost) as Cost
			from tblRefund
			group by OrderTaxID) as T2 on T1.OrderTaxID = T2.OrderTaxID
	where T1.OrderID = @OrderID
		and T1.Amount > coalesce( T2.Cost, 0 )

	if @SelectNewRefunds = 1
		select * 
		from tblRefund
		where OrderID = @OrderID
			and Cost = 0

	RETURN 0